For anyone out there working with SQL Server on a large or mission critical system it’s quite likely that, unless you’re the DBA, you’re not going to have dbo access to the databases you’re working with, particularly if you’re working directly with the production servers. Unsurprisingly then we’ve had a number of complaints from people finding themselves in exactly this situation who want to be able to use SQL Prompt 3, but can’t because it generally doesn’t work unless you’re dbo.

Fortunately my colleague Jon this morning let me know that a solution is at hand for SQL Server 2005 at least, although you’re still going to have to be nice to your DBA to make this work (I’d suggest that beer often helps). What you need to do is ask him or her to grant you the VIEW DEFINITION permission on the database of interest. What this means is that you’ll be able to see all the meta-data for any object defined in the database, but you won’t have access to the objects themselves, so for example you won’t be able to read any data from a table unless you have the permissions required to do so.

For example, say your username is “bill” and you need access to the AdventureWorks database, then your DBA would need to execute the following command:

Now, just to be clear about exactly what access this gives you, here’s an extract from SQL Server 2005 books online that explains how this permission works:

“The VIEW DEFINITION permission lets a user see the metadata of the securable on which it is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions, the user cannot read data from the table.”

You might still be curious about why SQL Prompt needs this permission, and the reason has to do with the fact that back in the mists of time (last May) we decided to use the SQL Compare back-end to retrieve our meta-data. Now this is a great example of software reuse, and it saved us an awful lot of development time, however when you reuse something for a purpose for which it wasn’t originally intended you can’t really be surprised if you encounter the odd hiccup along the way.

The problem here is that SQL Compare doesn’t trust the SQL Server system tables or views (wisely if you think about what it’s supposed to do) so it works out most of what it needs to know by parsing the SQL definitions for the objects in your database, and if it can’t get at the definition then it can’t build up a model of your database. Now for SQL Prompt having partial information is OK since giving people some help is probably better than giving them no help whatsoever, but if you want to find out what the differences are between two databases then anything less than complete and accurate information really won’t do at all, and SQL Compare therefore gets mardy as you like if it can’t get the information it needs.

You can be sure that we’ll be working on fixing this in the future but in the meantime cultivate kindness towards your DBA and ask them nicely to grant you the VIEW DEFINITION permission on your database and all will be well.